﻿using System;
using System.Collections.Generic;
using System.Data;
using CoreLab.Oracle;
using LMD.Lib.Oracle;
using LMD.Lib.DataClass;

namespace Controller
{
    /// <summary>
    /// Summary description for Activity Controller
    /// </summary>
    public class NPSTypeController : ControllerBase
    {
        public int InsertNPSTypeData(Entities.NPSType ciType)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "out_new_id";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Output;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_TITLE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciType.sxTitle;
            
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATOR";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            String sxQuery = "PR_I_P_NPS_TYPE";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
            return Convert.ToInt32(lsParams[0].Value);
        }

        public void UpdateNPSTypeData(Entities.NPSType ciType)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_TYPE_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciType.iID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_TITLE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciType.sxTitle;
            
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATOR";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciType.sxCreator;

            String sxQuery = "PR_U_P_NPS_TYPE";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }

        public void DeleteNPSTypeData(Entities.NPSType ciType)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_TYPE_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciType.iID;

            String sxQuery = "PR_D_P_NPS_TYPE";
            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }

        public List<Entities.NPSType> NPSTypeList
        {
            get
            {
                DBLib dbl = new DBLib();

                String sxQuery =
                    @"select 
                    P_NPS_TYPE_ID,
	                TITLE,
	               
	                CREATE_BY,
	                CREATE_DATE,
	               
	                VALID_FROM,
	                VALID_TO
                from 
                    P_NPS_TYPE
                where 
                    NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE
                ";
                DataTable dt = dbl.GetDataTable(sxQuery);

                List<Entities.NPSType> TypeList = new List<Entities.NPSType>();
                foreach (DataRow row in dt.Rows)
                {
                    Entities.NPSType Type = new Entities.NPSType();
                    Type.iID = Convert.ToInt16(row["P_NPS_TYPE_ID"].ToString());
                    Type.sxTitle = row["TITLE"].ToString();
                    Type.sxCreator = row["CREATE_BY"].ToString();
                    Type.dtmCreated = DateTime.Parse(row["CREATE_DATE"].ToString());
                    Type.dtmValidFrom = DateTime.Parse(row["VALID_FROM"].ToString());
                    TypeList.Add(Type);
                }
                return TypeList;
            }
        }

        public List<Entities.NPSType> find(string Search)
        {
                DBLib dbl = new DBLib();

                String sxQuery =
                    @"select 
                    P_NPS_TYPE_ID,
	                TITLE,
	               
	                CREATE_BY,
	                CREATE_DATE,
	               
	                VALID_FROM,
	                VALID_TO
                from 
                    P_NPS_TYPE
                where 
                   NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE and
                    upper(Title) like upper('%" + Search + @"%')
                ";
                DataTable dt = dbl.GetDataTable(sxQuery);

                List<Entities.NPSType> TypeList = new List<Entities.NPSType>();
                foreach (DataRow row in dt.Rows)
                {
                    Entities.NPSType Type = new Entities.NPSType();
                    Type.iID = Convert.ToInt16(row["P_NPS_TYPE_ID"].ToString());
                    Type.sxTitle = row["TITLE"].ToString();
                    Type.sxCreator = row["CREATE_BY"].ToString();
                    Type.dtmCreated = DateTime.Parse(row["CREATE_DATE"].ToString());
                    Type.dtmValidFrom = DateTime.Parse(row["VALID_FROM"].ToString());
                    TypeList.Add(Type);
                }
                return TypeList;
            
        }

        public Entities.NPSType getNPSType(int TypeID)
        {
            DBLib dbl = new DBLib();
            String sxQuery =
                @"select 
                    P_NPS_TYPE_ID,
	                TITLE,
	               
	                CREATE_BY,
	                CREATE_DATE,
VALID_FROM,
VALID_TO
                from 
                    P_NPS_TYPE
                where 
                    NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE and
                    P_NPS_TYPE_ID = :prmID
                ";

            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = TypeID;
            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
            Entities.NPSType Type = new Entities.NPSType();
            try
            {
                Type.iID = Convert.ToInt16(dt.Rows[0]["P_NPS_TYPE_ID"].ToString());
                Type.sxTitle = dt.Rows[0]["TITLE"].ToString();
                Type.sxCreator = dt.Rows[0]["CREATE_BY"].ToString();
                Type.dtmCreated = DateTime.Parse(dt.Rows[0]["CREATE_DATE"].ToString());
                Type.dtmValidFrom = DateTime.Parse(dt.Rows[0]["VALID_FROM"].ToString());

            }
            catch (Exception p) { errLog = p.ToString(); }
            return Type;
        }

        public Entities.NPSType getNPSType(string Typedata)
        {
            DBLib dbl = new DBLib();
            String sxQuery =
                @"select 
                    P_NPS_TYPE_ID,
	                TITLE,
	               
	                CREATE_BY,
	                CREATE_DATE,
VALID_FROM,
VALID_TO
                from 
                    P_NPS_TYPE
                where 
                    NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE and
                   upper(title) = upper(:prmID)
                ";

            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = Typedata.ToUpper();
            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
            Entities.NPSType Type = new Entities.NPSType();
            try
            {
                Type.iID = Convert.ToInt16(dt.Rows[0]["P_NPS_TYPE_ID"].ToString());
                Type.sxTitle = dt.Rows[0]["TITLE"].ToString();
                Type.sxCreator = dt.Rows[0]["CREATE_BY"].ToString();
                Type.dtmCreated = DateTime.Parse(dt.Rows[0]["CREATE_DATE"].ToString());
                Type.dtmValidFrom = DateTime.Parse(dt.Rows[0]["VALID_FROM"].ToString());

            }
            catch (Exception p) { errLog = p.ToString(); }
            return Type;
        }

    }
}